package com.xiyue.leaspring.datasourceTest;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.xiyue.leaspring.vo.News;

@ContextConfiguration(locations = {"classpath:spring/spring-jdbc8.xml"})
@RunWith(SpringJUnit4ClassRunner.class)//设置要测试的工具
public class DataSourceTest8 {
	
	private Logger logger = LoggerFactory.getLogger(DataSourceTest8.class);
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
		
	/**
	 * 查询单个
	 * @throws Exception
	 */
	@Test
	public void testSingle() throws Exception {
		
		String sql = "select nid,title,pubdate,note,price,readcount from news where nid=?";
		News vo = this.jdbcTemplate.queryForObject(sql, new Object[]{1006},new RowMapper<News>() {

			@Override
			public News mapRow(ResultSet rs, int rowNums) throws SQLException {
				News vo = new News();
				vo.setNid(rs.getLong(1));
				vo.setTitle(rs.getString(2));
				vo.setPubDate(rs.getDate(3));
				vo.setNote(rs.getString(4));
				vo.setPrice(rs.getDouble(5));
				vo.setReadCount(rs.getInt(6));
				return vo;
			}
			
		});
		this.logger.info(vo.toString());
		
	}
	
	/**
	 * 数据分页查询
	 */
	@Test
	public void testPageQuery() {
		String clumn="title";//查询列
		String keyWord = "大山脚下";//查询关键字
		Long currentPage = 1L;//当前所在页
		Integer lineSize = 5;//每页显示数据行
		
		String sql = "select nid,title,pubdate,note,price,readcount from news where "
				+ clumn + " like ? limit ?,?";
		List<News> vo = this.jdbcTemplate.query(sql, new Object[]{"%"+keyWord+"%",
				(currentPage-1)*lineSize,lineSize},new RowMapper<News>() {

			@Override
			public News mapRow(ResultSet rs, int rowNums) throws SQLException {
				News vo = new News();
				vo.setNid(rs.getLong(1));
				vo.setTitle(rs.getString(2));
				vo.setPubDate(rs.getDate(3));
				vo.setNote(rs.getString(4));
				vo.setPrice(rs.getDouble(5));
				vo.setReadCount(rs.getInt(6));
				return vo;
			}
			
		});
		this.logger.info(vo.toString());
	}
	
	/**
	 * 统计查询
	 */
	@Test
	public void testCount() {
		String clumn="title";//查询列
		String keyWord = "大山脚下";//查询关键字
		
		String sql = "select count(*)from news where "
				+ clumn + " like ?";
		Long count = this.jdbcTemplate.queryForObject(sql,Long.class,new Object[]{"%"+keyWord+"%"});
		this.logger.info("统计结果："+count);
	}
	
	

}
